﻿package com.sawgdev.android.bokuiki.common.database;

import com.sawgdev.android.bokuiki.bean.BeanCondition;
import com.sawgdev.android.bokuiki.bean.BeanConfig;
import com.sawgdev.android.bokuiki.bean.BeanHistory;
import com.sawgdev.android.bokuiki.common.Constants.EnumDateFormat;
import com.sawgdev.android.bokuiki.common.Util;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteStatement;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import java.util.Locale;

/**
 * ボクイキアプリ内で利用されるDBアクセス処理を担うクラス（Dao）
 *
 * @author y-yamaguchi
 *
 */
public class BkpfDao extends AbsBkpfDao {

	/**
	 * コンストラクタ
	 *
	 * @param db
	 *            SQLiteDataBase
	 */
	public BkpfDao(SQLiteDatabase db) {
		super(db);
	}

	@Override
	public long insHistory(BeanHistory beanHistory) {
		List<BeanHistory> list = new ArrayList<BeanHistory>();
		list.add(beanHistory);
		return insHistory(list);
	}

	@Override
	public long insHistory(List<BeanHistory> list) {
		long result = 0;

		StringBuffer sql = new StringBuffer();
		sql.append("INSERT INTO gpshistory ( latitude, longitude, altitude, location, status, create_date) VALUES ( ?, ?, ?, ?, ?, ?) ");

		db.beginTransaction();
		try {
			SQLiteStatement stmt = db.compileStatement(sql.toString());
			for (BeanHistory beanHistory : list) {
				stmt.bindDouble(1, beanHistory.getLatitude());
				stmt.bindDouble(2, beanHistory.getLongitude());
				stmt.bindDouble(3, beanHistory.getAltitude());
				stmt.bindString(4, beanHistory.getLocation());
				stmt.bindLong(5, beanHistory.getStatus());
				stmt.bindString(6, beanHistory.getCreateDate());

				stmt.executeInsert();
				result++;
			}
			db.setTransactionSuccessful();
		} finally {
			db.endTransaction();
			db.close();
		}

		return result;
	}

	@Override
	public List<BeanHistory> selTracks() {
		return selTracks(null);
	}

	@Override
	public List<BeanHistory> selTracks(BeanCondition beanCondition) {
		List<BeanHistory> result = new ArrayList<BeanHistory>();

		if(beanCondition == null){
			beanCondition = new BeanCondition();
		}

		StringBuffer sql = new StringBuffer();
		sql.append("SELECT _id, latitude, longitude, altitude, location, status, create_date FROM gpshistory");
		sql.append("  WHERE ");
		sql.append("    create_date >= ? ");
		sql.append("  ORDER BY _id DESC");

		// 検索条件となる○分前の日時情報を保持したCalendarを取得
		Calendar cal = Util.addMinutes(Calendar.getInstance(Locale.JAPANESE),
				-1 * beanCondition.getMinutesAgo());

		// ○分前の日時を年月日時分秒文字列に変換
		String fromDt = Util.calendarToStr(cal, EnumDateFormat.FORMAT_FULL);

		Cursor c = db.rawQuery(sql.toString(), new String[] { fromDt });
		BeanHistory bean = null;
		while(c.moveToNext()) {
			bean = new BeanHistory();
			bean.setId(c.getLong(c.getColumnIndex("_id")));
			bean.setLatitude(c.getDouble(c.getColumnIndex("latitude")));
			bean.setLongitude(c.getDouble(c.getColumnIndex("longitude")));
			bean.setAltitude(c.getDouble(c.getColumnIndex("altitude")));
			bean.setLocation(c.getString(c.getColumnIndex("location")));
			bean.setStatus(c.getInt(c.getColumnIndex("status")));
			bean.setCreateDate(c.getString(c.getColumnIndex("create_date")));
			result.add(bean);
		}
		c.close();
		db.close();

		return result;
	}

	@Override
	public long mrgConfig(BeanConfig beanConfig) {
		long result = 0;
		int cnt = cntConfig();
		db.beginTransaction();
		try {

			// 1件も設定情報が存在しない場合は
			SQLiteStatement stmt = null;

			if (cnt == 0) {
				// INSERT文
				StringBuffer insSql = new StringBuffer();
				insSql.append("INSERT INTO config ( name, name_kana, birth, sex, tel, email, etc, address, lat, lng, alert_notification ) ");
				insSql.append(" VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ");

				stmt = db.compileStatement(insSql.toString());
			} else {
				// UPDATE文
				StringBuffer updSql = new StringBuffer();
				updSql.append("UPDATE config SET ");
				updSql.append(" name = ? ");
				updSql.append(" , name_kana = ? ");
				updSql.append(" , birth = ? ");
				updSql.append(" , sex = ? ");
				updSql.append(" , tel = ? ");
				updSql.append(" , email = ? ");
				updSql.append(" , etc = ? ");
				updSql.append(" , address = ? ");
				updSql.append(" , lat = ? ");
				updSql.append(" , lng = ? ");
				updSql.append(" , alert_notification = ? ");

				stmt = db.compileStatement(updSql.toString());
			}
			stmt.bindString(1, beanConfig.getName());
			stmt.bindString(2, beanConfig.getNameKana());
			stmt.bindString(3, beanConfig.getBirth());
			stmt.bindLong(4, beanConfig.getSex());
			stmt.bindString(5, beanConfig.getTel());
			stmt.bindString(6, beanConfig.getEmail());
			stmt.bindString(7, beanConfig.getEtc());
			stmt.bindString(8, beanConfig.getAddress());
			stmt.bindDouble(9, beanConfig.getLat());
			stmt.bindDouble(10, beanConfig.getLng());
			stmt.bindLong(11, beanConfig.getAlertNotification());

			stmt.executeInsert();
			result++;

			db.setTransactionSuccessful();
		} finally {
			db.endTransaction();
			db.close();
		}

		return result;
	}

	/**
	 * 基本設定情報の件数を取得します。
	 * @return 件数
	 */
	private int cntConfig() {
		int result = 0;
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT _id FROM config");
		Cursor c = db.rawQuery(sql.toString(), null);
		result = c.getCount();
		c.close();
		return result;
	}

	@Override
	public BeanConfig selConfig() {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT _id, name, name_kana, birth, sex, tel, email, etc, address, lat, lng, alert_notification FROM config");
		Cursor c = db.rawQuery(sql.toString(), null);

		BeanConfig result = new BeanConfig();
		if (c.moveToFirst()) {
			result.setId(c.getLong(c.getColumnIndex("_id")));
			result.setName(c.getString(c.getColumnIndex("name")));
			result.setNameKana(c.getString(c.getColumnIndex("name_kana")));
			result.setBirth(c.getString(c.getColumnIndex("birth")));
			result.setSex(c.getInt(c.getColumnIndex("sex")));
			result.setTel(c.getString(c.getColumnIndex("tel")));
			result.setEmail(c.getString(c.getColumnIndex("email")));
			result.setEtc(c.getString(c.getColumnIndex("etc")));
			result.setAddress(c.getString(c.getColumnIndex("address")));
			result.setLat(c.getDouble(c.getColumnIndex("lat")));
			result.setLng(c.getDouble(c.getColumnIndex("lng")));
			result.setAlertNotification(c.getInt(c.getColumnIndex("alert_notification")));
		}
		c.close();
		db.close();
		return result;
	}
}
